# """
# python使用django数据库, 实现增、删、查、改。供服务器使用 

# 本数据库使用两张表，共用一个增、删、查、改方法
# from blog.models import UserInfo
# from blog.models import Msg

# >>> import django
# >>> print(django.VERSION)
# (1, 11, 5, 'final', 0)

# winXP python3.4.3 测试通过   2022.12.13

# 快速运行本脚本
# C:\Documents and Settings\Administrator>runDjDB_UserInfo_Msg_API_V1.bat
# """

import os
import sys
import django
# python使用django数据库，下面两条语句是必须的
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "mysite.settings")
django.setup()

from blog.models import UserInfo
from blog.models import MsgInfo
from django.forms.models import model_to_dict

####建议使用
def add(dct, mode):
	ret = False
	try:
		mode.objects.create(**dct)
		ret = True
	except Exception as ex:
		print("err: %s"%ex)
	return ret

def addMac(dct, mode):
	'''如果macAddress存在，则更新name、password '''
	if is_macAddress(dct.get("macAddress", ""), mode):
		return mode.objects.filter(macAddress=dct.get("macAddress", "")).update(name=dct.get("name", ""), password=dct.get("password", "")) #更新
	return add(dct, mode)

def find_macaddrs(macAddress, mode):
	'''查找macaddrs记录'''
	return mode.objects.filter(macAddress=macAddress)


def is_macAddress(macAddress, mode):
	'''判断macaddrs记录是否存在'''
	ret = False
	if mode.objects.filter(macAddress=macAddress):
		ret = True
	return ret

def is_name_pass(name, password, mode):
    '''判断数据库中是否有name, pass记录'''
    ret = False
    if mode == UserInfo:
    	if mode.objects.filter(name=name, password=password): 
    		ret = True
    return ret

def is_name(name, mode):
    '''判断数据库中是否有name记录'''
    ret = False
    if mode.objects.filter(name=name):
    	ret = True
    return ret

def is_name_pass(name, password, mode):
    '''判断数据库中是否有name, pass记录'''
    ret = False
    if mode == UserInfo:
    	if mode.objects.filter(name=name, password=password):
    		ret = True
    return ret

def update_password_name(name, password, mode):
    '''由name更新password记录'''
    ret = False
    if mode == UserInfo:
    	if mode.objects.filter(name=name).update(password=password):
    		ret = True
    return ret

def del_macAddress(macAddress, mode):
    '''删除macaddrs记录...'''
    ret = False
    if mode.objects.filter(macAddress=macAddress).delete()[0]:
    	ret = True
    return ret

def del_all(mode):
    '''
    删除全部记录.
    '''
    ret = False
    if mode.objects.all().delete():
    	ret = True
    return ret

def del_id(id, mode):
    '''
    删除id记录.
    '''
    ret = False
    if mode.objects.filter(id=id).delete()[0]:
    	ret = True
    return ret

def del_name(name, mode):
    '''
    删除name记录.
    '''
    ret = False
    if mode.objects.filter(name=name).delete()[0]:
    	ret = True
    return ret


def QuerySetToList(query):
	'''
	记录转换为Query列表
	query： <QuerySet [<User: admin>, <User: wu>]> 
	return: Query列表 <QuerySet [('admin', '123', '558899'), ('wu', '123', '668899')]>
	'''
	return query.values_list()

def getScope(mode, start, mlen):
	'''
	按区间查询数据库记录 2022.12.31
 	start： 开始指针（从 0开始）
	mlen：获取记录数
	'''
	return	mode.objects.all()[start:start+mlen]

def getFrontScope(mode, startPosition, mlen):
	'''
 	 *   按区间，由前向后，获得数据库记录。为动态分页获取数据而设。 2022.12.31
	 *   startPosition 开始位置。
	 *   lenNum 长度。 
	 *   startPosition=0, mlen=5 获得开始5条记录。
	'''
	datas = mode.objects.filter(macAddress="") #初值，空记录
	if((len(mode) > 0) & (startPosition >= 0) & (startPosition < len(mode)) & (mlen > 0)) :
		if((startPosition+mlen) <= len(mode)):
			datas = getScope(mode, startPosition, mlen) 
		else: #最后
			datas = getScope(mode, startPosition, len(mode)-startPosition)
		
	return datas


def getPageFrontScope(mode, pagePosition, mlen):
	'''
 	 *   按区间，由前向后，获得数据库记录。为动态分页获取数据而设。 2022.12.31
	 *   pagePosition 页指针。0, 1, 2, 3,...
	 *   lenNum 长度。 
	 *   pagePosition=0, mlen=5 获得开始5条记录。
	'''
	datas = mode.objects.filter(macAddress="") #初值，空记录
	if((len(mode) > 0) & (pagePosition >= 0) & (mlen > 0)) :
		if(pagePosition*mlen <= len(mode)):
			datas = getScope(mode, pagePosition*mlen, mlen) 
		else: #最后pagePosition*mlen>len(mode)
			datas = getScope(mode, (pagePosition-1)*mlen, len(mode)-(pagePosition-1)*mlen)
		
	return datas

def getFrontListDict(mode, pagePosition, mlen, fieldsList=None, excludeList=None):
	'''
 	 *   按区间，由前向后，获得数据库记录（列表字典）。为动态分页获取数据而设。 
	 *   pagePosition 页指针。0, 1, 2, 3,...
	 *   lenNum 长度。 
	 *   pagePosition=0, mlen=5 获得开始5条记录。
	 print(getFrontListDict(MsgInfo, 0, 2)) #获得全部字段
	 print(getFrontListDict(MsgInfo, 0, 2, ["macAddress", "name", "ip"])) #获得3个字段
	 print(getFrontListDict(MsgInfo, 0, 2, None, ["personMacAddress", "personName", "personIp", "personHeadicon", "unReadMsgCount", "time"])) #获得排除6个字段记录  
	'''	
	lst = []
	if fieldsList:
		for i in getPageFrontScope(mode, pagePosition, mlen):		
			lst.append(model_to_dict(i, fields=fieldsList))
	elif excludeList:
		for i in getPageFrontScope(mode, pagePosition, mlen):
			lst.append(model_to_dict(i, exclude=excludeList))
	else:
		for i in getPageFrontScope(mode, pagePosition, mlen):		
			lst.append(model_to_dict(i))		
	return lst

def toAll(mode, mlen):
	'''
	一次查找mlen条记录，获得全部记录.一次获得全部记录只能用于测试。
	mode：数据库表名
	mlen：一次查找mlen条记录
	'''
	items =  mode.objects.filter(macAddress="") #初值，空记录
	for i in range(0, mode.objects.all().count(), mlen):
		items = items | mode.objects.all()[i:i+mlen]
	return items

####建议使用
def getMsgInfoName(mode, personIp):
	'''
	获得personIp字段记录，name字段的数据列表 2022.12.31
	<QuerySet ['20221230130512.amr', '20221230131055.amr']>
	'''
	return  mode.objects.filter(personIp=personIp).values_list("name", flat=True) 

####建议使用
def getDicts(mode):
	'''
	列表字典形式，获得数据库记录 2022.12.31
	print(list(getDicts(MsgInfo))[-1]) #支持切片
	'''
	return mode.objects.values()

####建议作用
def getPersonIpDicts(mode, personIp):
	'''
	列表字典形式，获得字段personIp unReadMsgCount=0 的数据库记录 2022.12.31
	print(list(getPersonIpDicts(MsgInfo, "192.168.31.5"))[-1]) #支持切片
	'''
	return mode.objects.filter(personIp=personIp, unReadMsgCount=0).values()

def find_all(mode):
	'''
	查找全部记录。一次获得全部记录只能用于测试。
	return: <QuerySet [<User: admin>, <User: wu>]> 
	'''
	return mode.objects.all() 

####建议使用  
def len(mode):
	return mode.objects.all().count() 

if __name__ == '__main__':
	#print(QuerySetToList(find_all(UserInfo)))
	#print("长度：", len(UserInfo)) #支持切片
	print(list(getDicts(UserInfo))) #支持切片

	# personIp = "192.168.31.5"
	# print(len(MsgInfo))
	# names = MsgInfo.objects.filter(personIp=personIp, unReadMsgCount=0).values_list("name", flat=True) 
	# for i in names:
	# 	if ".amr" in i:
	# 		print(i)
	#print(list(getDicts(MsgInfo))[0:2])	
	#print(list(getDicts(MsgInfo))[-1])
	#print(getPersonIpDicts(MsgInfo, "192.168.31.5"))
	#fieldsList = ["macAddress", "name", "ip", "personMacAddress", "personName", "personIp", "time"]
	#excludeList = ["personMacAddress", "persoName", "personIp", "time"]
	#print(del_all(MsgInfo))
	
	#print(MsgInfo.objects.filter().values_list())  #[-3:]截取倒数第三位到结尾
	#print(getFrontListDict(MsgInfo, 0, 2)) #获得全部字段
	#print(getMsgInfoName(MsgInfo, "192.168.31.7")[0:2])
	#print(getFrontListDict(MsgInfo, 0, 2, ["macAddress", "name", "ip"])) #获得3个字段
	#print(getFrontListDict(MsgInfo, 0, 2, None, ["personMacAddress", "personName", "personIp", "personHeadicon", "unReadMsgCount", "time"])) #获得排除6个字段记录  
	

	#print(QuerySetToList(toAll(MsgInfo, 3)))
	#print(toListDict(MsgInfo, 1, fieldsList)[-1])  #获得最后一条记录
	#print(getListDict(MsgInfo)[-1])  #获得最后一条记录
    
	# fieldsList = ["macAddress", "name", "ip", "unReadMsgCount", "headicon", "time", "ack"]
	# print(len(UserInfo))
	# print(QuerySetToList(toAll(UserInfo, 3)))
	# print(toListDict(UserInfo, 4, fieldsList))




	# 测试 UserInfo表
	'''
	dct1 = {"macAddress":"94fe2271ac0c", "name":"随缘", "password":"123", "ip":"192.168.31.5", \
	"unReadMsgCount":0, "headicon":0, "ask":0, "time":0}
	dct2 = {"macAddress":"70720d06e84b", "name":"大海", "password

	rord":"123", "ip":"192.168.31.5", \
	"unReadMsgCount":0, "headicon":0, "ask":0, "time":0}
	
	print(add(dct1, UserInfo))  #True
	print(add(dct2, UserInfo))  #True
	print(QuerySetToList(find_all(UserInfo)))

	print(is_name_pass("随缘", "123", UserInfo))
	print(is_name_pass("随缘", "1234", UserInfo))
	print(del_all(UserInfo))  #True
	print(add(dct1, UserInfo))  #True
	print(add(dct2, UserInfo)) #True
	print(is_macAddress("94fe2271ac0c", UserInfo))  #True

	print(del_macAddress("94fe2271ac0c", UserInfo))  #True
	print(update_password_name("大海", "369", UserInfo))  #True
	print(del_macAddress("70720d06e84b", UserInfo))  #True
	print(QuerySetToList(find_all(UserInfo))) 

	# 测试 Msg 表
	
	dct1 = {"macAddress":"94fe2271ac0c", "name":"随缘", "ip":"192.168.31.5", "time":0, \
	"msgType":0, "msgContent":"消息内容", "msgCMD":0, "receMacAddress":"70720d06e84b"}
	print(add(dct1, Msg))  #True
	print(del_all(Msg))  #True
	print(add(dct1, Msg))  #True
	print(is_macAddress("94fe2271ac0c", Msg))  #True
	print(QuerySetToList(find_all(Msg))) #True
	print(is_name("随缘", Msg))  #True 
	print(del_name("随缘", Msg))  #True 
	print(QuerySetToList(find_all(Msg)))

#https://blog.csdn.net/s_frozen/article/details/125291960
'''